import oracle.jdbc.pool.OracleDataSource;
import java.sql.*;
import java.util.ArrayList;
import java.util.Calendar;
import java.text.*;

/**
 * Created by IntelliJ IDEA.
 * User: MrN
 * Date: 21.11.11
 * Time: 20:35
 * To change this template use File | Settings | File Templates.
 */
public class SqlBot
{
    private OracleDataSource ods;
    private Connection conn;
    private Statement stmt;
    private PreparedStatement ps;
    private RSSReaderDOM parser;


    static final String connect_string = "jdbc:oracle:thin:Gruppe1/Gruppe12011@//pauro.hin.no:1521/orcl";

    /***********************************************************************************************************
     SqlBot():: 1th Constructor for primary database connection
    ***********************************************************************************************************/
    public SqlBot() throws ClassNotFoundException, SQLException
    {
        parser = new RSSReaderDOM();
        Class.forName("oracle.jdbc.OracleDriver");
        System.out.println("Driver for Oracle loaded OK");

        // Create a OracleDataSource instance and set URL
        ods = new OracleDataSource();
        ods.setURL(connect_string);

        conn = ods.getConnection();
    }

    /***********************************************************************************************************
      SqlBot():: 2th constructor for custom database connection.
      sqlBot bot2 = new sqlBot("Gruppe1", "Gruppe12011", "pauro.hin.no", 1521);
    ***********************************************************************************************************/
    public SqlBot(String userName, String passwd, String url, int port) throws ClassNotFoundException, SQLException
    {
        parser = new RSSReaderDOM();
        String connect = "jdbc:oracle:thin:" + userName + "/" + passwd + "@" + url + ":" + port + "/orcl";
        Class.forName("oracle.jdbc.OracleDriver");
        System.out.println("Driver for Oracle loaded OK");

        // Create a OracleDataSource instance and set URL
        ods = new OracleDataSource();
        ods.setURL(connect);

        conn = ods.getConnection();
    }

    /***********************************************************************************************************
     getURL():: This function retrieves all the feed URLS and stores them in a ArrayList for processing.
     **********************************************************************************************************/
    public ArrayList<String> getURL() throws ClassNotFoundException, SQLException
    {
        ArrayList<String> urls = new ArrayList<String>();
        conn = ods.getConnection();

        conn.setAutoCommit(false);
        stmt = conn.createStatement();
        ResultSet rset = stmt.executeQuery("select URL from NyhetsByrå");
        while (rset.next())
        {
          urls.add(rset.getString(1));
        }
        return urls;
    }

    /***********************************************************************************************************
     addNews():: This function loops-through all the urls and passes them on for single-news creation.
     **********************************************************************************************************/
    public void addNews() throws ClassNotFoundException, SQLException
    {
        ArrayList<News> listOfNews;


        ArrayList<String> urls =  getURL();

        for(String value : urls)
        {
               listOfNews = parser.getNews(value);
               for(News news : listOfNews)
               {
                   addSingleNews(news, value);
               }
        }
    }

    /***********************************************************************************************************
     checkSingleNews():: This function check if a single news is already existing in the database.
     **********************************************************************************************************/
    private boolean checkSingleNews(String inIngress) throws ClassNotFoundException, SQLException
    {
        String query = "SELECT OVERSKRIFT FROM NYHET";
        ResultSet checkNyhet;
        boolean exist = false;

        try
        {
            checkNyhet = stmt.executeQuery(query);

            while(checkNyhet.next())
            {
                if(checkNyhet.getString(1).equals(inIngress))
                {
                    exist = true;
                }
            }

        } catch(NullPointerException e) { }

        return exist;

    }

    /***********************************************************************************************************
     addsingleNews():: This function creates an entry in the Nyhets-table
    ***********************************************************************************************************/
    private void addSingleNews(News value, String baseUrl) throws ClassNotFoundException, SQLException
    {
        if(!checkSingleNews(value.getTitle()))
        {
            stmt = conn.createStatement();
            PreparedStatement statement = null;
            String SQL_INSERT = "INSERT INTO NYHET VALUES(?, ?, ?, ?, ?, ?, ?)";

            statement = conn.prepareStatement(SQL_INSERT);
            conn.setAutoCommit(true);


            int id = getMaxID("Nyhet");
            if(id >= 0)
            {
                id++;
            }

            String url = value.getUrl();
            String title = value.getTitle();
            String description = value.getDescription();
            String pubDate = value.getDate();

            int firmID = getFirmID(baseUrl);
            int newsType = getNewsType(firmID);
            statement.setInt(1, id);
            statement.setString(2, url);
            statement.setString(3, title);
            statement.setString(4, description);
            statement.setString(5, pubDate);
            statement.setInt(6, firmID);
            statement.setInt(7, newsType);
            statement.executeUpdate();
            System.out.println("News added.");
        }
        else { System.out.println("News exists"); }
    }

    /***********************************************************************************************************
     getMaxID():: This function return the Max-ID from given table based on given String value
    ***********************************************************************************************************/
    public int getMaxID(String value) throws ClassNotFoundException, SQLException
    {
        stmt = conn.createStatement();
        int result = 1;
        String query = "SELECT MAX(id) FROM " + value;
        ResultSet getID = stmt.executeQuery(query);

        while(getID.next())
        {
            try
            {
                result = getID.getInt(1);
            } catch(NullPointerException e) { System.out.println("Shit gone wrong bro"); }
        }
        return result;
    }

    /***********************************************************************************************************
     getFirmID():: This function return the Byrå-ID from given String value
    ***********************************************************************************************************/
    private int getFirmID(String value) throws ClassNotFoundException, SQLException
    {
        String query = "SELECT ID FROM NYHETSBYRÅ WHERE URL = '" + value + "'";
        ResultSet FirmID = stmt.executeQuery(query);
        int result = 0;
        while(FirmID.next())
        {
            result = FirmID.getInt(1);
        }
        return result;
    }

    /***********************************************************************************************************
     getNewsType():: This function return the news-type ID from a Byrå
    ***********************************************************************************************************/
    private int getNewsType(int value) throws ClassNotFoundException, SQLException
    {
        String query = "SELECT Type FROM NYHETSBYRÅ WHERE ID = '" + value + "'";
        ResultSet NewsType = stmt.executeQuery(query);
        int result = 0;
        while(NewsType.next())
        {
            result = NewsType.getInt(1);
        }
        return result;
    }

    /***********************************************************************************************************
     getNyhetsTypeID():: This function returns the gives News-type ID from a given String value
    ***********************************************************************************************************/
    private int getNYHETSTYPEID(String value) throws ClassNotFoundException, SQLException
    {
        String query = "SELECT ID FROM NYHETSTYPE WHERE BESKRIVELSE = '" + value + "'";
        ResultSet NewsType = stmt.executeQuery(query);
        int result = 0;
        while(NewsType.next())
        {
            result = NewsType.getInt(1);
        }
        return result;
    }

    /***********************************************************************************************************
     addByraaType():: This function creates an entry in the reference-table with byrå-id and news-type-id
    ***********************************************************************************************************/
    public void addByraaType(int byraa, int type) throws ClassNotFoundException, SQLException
    {
        stmt = conn.createStatement();
        PreparedStatement statement = null;

        String query = "INSERT INTO BYRÅNYHETSTYPE VALUES(?,?)";

        statement = conn.prepareStatement(query);
        conn.setAutoCommit(true);

        statement.setInt(1, byraa);
        statement.setInt(2, type);
        statement.executeUpdate();
    }

    /***********************************************************************************************************
     checkByraaType():: This function check the give a Byrå-ID and news-type-ID exists in the referrence table
    ***********************************************************************************************************/
    private boolean checkByraaType(int byraa, int type) throws ClassNotFoundException, SQLException
    {
        String query = "SELECT * FROM BYRÅNYHETSTYPE";
        ResultSet NewsType = stmt.executeQuery(query);
        boolean exist = false;

        while(NewsType.next())
        {
            if(NewsType.getInt(1) == byraa && NewsType.getInt(2) == type)
            {
                exist = true;
            }
        }
        return exist;
    }

    /***********************************************************************************************************
     checkNyhetsByraa():: This function adds a noen-existing news-type
    ***********************************************************************************************************/
    private boolean checkNyhetsByraa(String url) throws ClassNotFoundException, SQLException
    {
        String query = "SELECT url FROM NYHETSBYRÅ";
        ResultSet checkByraa;
        boolean exist = false;

        try
        {
            checkByraa = stmt.executeQuery(query);

            while(checkByraa.next())
        {
            if(checkByraa.getString(1).equals(url))
            {
                exist = true;
            }
            else {  }
        }

        } catch(NullPointerException e) { }

        return exist;
    }

    /***********************************************************************************************************
     addByraa():: This function creates a "Byrå"  and returns the ID value
    ***********************************************************************************************************/
    private int addByraa(String name, String type, String url) throws ClassNotFoundException, SQLException
    {
        stmt = conn.createStatement();
        PreparedStatement statement = null;

        String query = "INSERT INTO NYHETSBYRÅ VALUES(?,?,?,?,?,?,?)";

        statement = conn.prepareStatement(query);
        conn.setAutoCommit(true);

        int id = getMaxID("Nyhetsbyrå");
        if(id >= 0)
        {
            id++;
        }
        String date = now("dd.mm.yyyy");
        int typeNr = getNYHETSTYPEID(type);

        statement.setInt(1, id);
        statement.setString(2, name);
        statement.setInt(3, 5);
        statement.setString(4, date);
        statement.setInt(5, typeNr);
        statement.setString(6, now("dd.mm.yyyy"));
        statement.setString(7, url);
        statement.executeUpdate();
        return id;
    }

    /***********************************************************************************************************
     addFeed():: This function checks news-type and checks if the given news exist.
                 If not it creates both.
    ***********************************************************************************************************/
    public void addFeed(String name, String type, String url ) throws ClassNotFoundException, SQLException
    {
        int newsTypeID, byraaID;

        if(!checkNewsType(type))
        {
            System.out.println("News type added.");
            newsTypeID = addNewsType(type);

        } else
        {
            System.out.println("News type exists");
            newsTypeID = getNYHETSTYPEID(type);
        }

        if(!checkNyhetsByraa(url))
        {
            System.out.println("Byrå added");
            byraaID = addByraa(name, type, url);

            addByraaType(byraaID, newsTypeID);
        } else { System.out.println("Byrå exists"); }
    }

    /***********************************************************************************************************
     addNewsType():: This function adds a noen-existing news-type and returns the ID value
    ***********************************************************************************************************/
    public int addNewsType(String value) throws ClassNotFoundException, SQLException
    {

        String desc = value;
        int id = getMaxID("NyhetsType");
        id++;

        stmt = conn.createStatement();
        PreparedStatement statement = null;

        String query = "INSERT INTO NYHETSTYPE VALUES(?,?)";

        statement = conn.prepareStatement(query);
        conn.setAutoCommit(true);

        statement.setInt(1, (id));
        statement.setString(2, desc);
        statement.executeUpdate();
        return id;
    }

    /***********************************************************************************************************
     checkNewsType():: This function checks if the given news-type exists or not.
    ***********************************************************************************************************/
    public boolean checkNewsType(String value) throws ClassNotFoundException, SQLException
    {
        String query = "SELECT ID FROM NYHETSTYPE WHERE BESKRIVELSE = '" + value + "'";
        stmt = conn.createStatement();
        ResultSet getNewsType = stmt.executeQuery(query);
        boolean result = false;

        if(getNewsType.next())
        {
            result = true;

        }
        return result;
    }

    /***********************************************************************************************************
     now():: This function generates todays date and return the value.
    ***********************************************************************************************************/
    public static String now(String dateFormat)
        {
            Calendar cal = Calendar.getInstance();
            SimpleDateFormat sdf = new SimpleDateFormat(dateFormat);
            return sdf.format(cal.getTime());
        }

    /***********************************************************************************************************
     deleteNews():: This function delete all data from tabel nyhet
    ***********************************************************************************************************/
    public void deleteNews() throws ClassNotFoundException, SQLException, InterruptedException
    {
        System.out.println("Start deleting");

        stmt = conn.createStatement();
        String query = "DELETE FROM NYHET";
        stmt.executeQuery(query);

        System.out.println("Stop deleting");
    }

}